Documentation generated by Matillion ETL 
Schema | ${Schema_Default} |
New Table Name | MDM_LOV |
Create/Replace | Replace |
Table Metadata | ALIGNMENT STRUCTURE CODE, Text, 255, None, True, FRANCHISE CODE, Text, 255, None, True, ORG ID PREFIX, Text, 255, None, True, SALES ORG DIVISION CODE, Text, 255, None, True, SALES ORG NAME, Text, 255, None, True, DEFAULT BILL-TO, Text, 255, None, True, DEFAULT PAY-FROM, Text, 255, None, True, UPDATED BY, Text, 255, None, True, UPDATED DATE, date, 255, None, True |
Distribution Style | Auto |
Sort Key | |
Primary Key | |
Identity Columns | |
Backup Table | Yes |
Schema | ${Schema_Default} |
Target Table Name | mdm_lov |
Load Columns | alignment structure code, franchise code, org id prefix, sales org division code, sales org name, default bill-to, default pay-from, updated by, updated date |
S3 URL Location | s3://ethicon/outbound/One MD Testing /Input files/ |
S3 Object Prefix | MDM_LOV_Param.txt |
IAM Role ARN | arn:aws:iam::<aws-account-id>:role/<role-name> |
Data File Type | Delimited |
Delimiter | | |
Explicit IDs | No |
S3 Bucket Region | None |
Compression Method | None |
Encoding | UTF8 |
Remove Quotes | No |
Replace Invalid Characters | ? |
Maximum Errors | 0 |
Date Format | auto |
Time Format | auto |
Ignore Header Rows | 1 |
Accept Any Date | Yes |
Ignore Blank Lines | Yes |
Truncate Columns | No |
Fill Record | Yes |
Trim Blanks | Yes |
NULL As | \\N |
Empty As Null | Yes |
Blanks As Null | Yes |
Comp Update | On |
Stat Update | On |
Escape | No |
Round Decimals | Yes |
Manifest | No |
Schema | ${Schema_Default} |
Table Name | outbound_dest_eth_dl_mdm21_sales_rep_affiliation |
S3 URL Location | ${ONEMD_S3_Target_Folder} |
S3 Object Prefix | ETH MDM21 Sales Rep Affiliation.txt |
IAM Role Arn | arn:aws:iam::<aws-account-id>:role/<role-name> |
Generate Manifest | No |
Data File Type | Delimited |
Delimiter | | |
Compress Data | No |
Null As | |
Escape | No |
Allow Overwrites | Yes |
Parallel | No |
Add Quotes | No |
Max File Size (MB) | |
Include Header | No |
Encryption | None |
Main Table | Part-Geo: Filter Territory |
Main Table Alias | pgft |
Joins | Param: Geo Hire Filter Territory, ghft, Left |
Join Expressions | "pgft"."algn_struc_cd" = "ghft"."upper_algn_struc_cd" AND cast("pgft"."efftv_start_dt" as date) <= cast("ghft"."efftv_end_dt" as date) AND cast("pgft"."efftv_end_dt" as date) >= cast("ghft"."efftv_start_dt" as date), pgft_Left_ghft |
Output Columns | pgft.algn_struc_cd, algn_struc_cd, pgft.participant_id, participant_id, pgft.geo_id, geo_id, pgft.core_temp_cd, core_temp_cd, pgft.split_pct, split_pct, pgft.last_updated_dt, last_updated_dt, pgft.efftv_start_dt, efftv_start_dt, pgft.efftv_end_dt, efftv_end_dt, ghft.lower_algn_struc_cd, lower_algn_struc_cd, ghft.efftv_start_dt, new_efftv_start_dt, ghft.efftv_end_dt, new_efftv_end_dt |
Main Table | Part-Geo: Filter Not Null |
Main Table Alias | pgf |
Joins | Param: Alignment Structure, pas, Left |
Join Expressions | "pgf"."lower_algn_struc_cd" ="pas"."algn_struc_cd", pgf_Left_pas |
Output Columns | pgf.participant_id, participant_id, pgf.algn_struc_cd, pgf_algn_struc_cd, pgf.geo_id, geo_id, pgf.core_temp_cd, core_temp_cd, pgf.split_pct, split_pct, pgf.last_updated_dt, pgf_last_updated_dt, pgf.efftv_start_dt, pgf_efftv_start_dt, pgf.efftv_end_dt, pgf_efftv_end_dt, pgf.lower_algn_struc_cd, lower_algn_struc_cd, pgf.new_efftv_start_dt, new_efftv_start_dt, pgf.new_efftv_end_dt, new_efftv_end_dt, pgf.source_table, source_table, pas.last_updated_dt, load_date |
Main Table | Part-Geo: Merge Terr - Mgr |
Main Table Alias | pgtm |
Joins | Team-Geo: Adjust Dates, tg, Left |
Join Expressions | "pgtm"."algn_struc_cd" = "tg"."algn_struc_cd" AND "pgtm"."geo_id" = "tg"."geo_id" AND Cast("pgtm"."efftv_start_dt" as date) <= Cast("tg"."tg_efftv_end_dt" as date) AND Cast("pgtm"."efftv_end_dt" as date) >= Cast("tg"."tg_efftv_start_dt" as date), pgtm_Left_tg |
Output Columns | pgtm.participant_id, participant_id, pgtm.algn_struc_cd, algn_struc_cd, pgtm.geo_id, geo_id, pgtm.core_temp_cd, core_temp_cd, pgtm.split_pct, split_pct, pgtm.efftv_start_dt, efftv_start_dt, pgtm.efftv_end_dt, efftv_end_dt, pgtm.last_updated_dt, last_updated_dt, tg.tg_efftv_start_dt, efftv_start_dt_new, tg.tg_efftv_end_dt, efftv_end_dt_new, tg.team_id, team_id, tg.split_pct, tg_split_pct |
Include Input Columns | Yes |
Calculations | GREATEST (cast("efftv_start_dt" as date), cast("efftv_start_dt_new" as date)), efftv_start_dt, LEAST (cast("efftv_end_dt" as date), cast("efftv_end_dt_new" as date)), efftv_end_dt, CASE WHEN "team_id" IS NULL OR "team_id" = '' THEN "geo_id" ELSE "team_id" END, team_id, CASE WHEN "split_pct" IS NULL OR "split_pct" = '' THEN '1.0000' ELSE "split_pct" END, split_pct |
Main Table | Part-Geo: Merge Terr - Mgr |
Main Table Alias | pgmtm |
Joins | MDM LOV Parameters, lov, Inner |
Join Expressions | "pgmtm"."algn_struc_cd" = "lov"."alignment structure code", pgmtm_Inner_lov |
Output Columns | pgmtm.algn_struc_cd, algn_struc_cd, pgmtm.participant_id, participant_id, pgmtm.geo_id, geo_id, pgmtm.core_temp_cd, core_temp_cd, pgmtm.split_pct, split_pct, pgmtm.efftv_start_dt, efftv_start_dt, pgmtm.efftv_end_dt, efftv_end_dt, pgmtm.last_updated_dt, last_updated_dt, lov.org id prefix, org id prefix, lov.default bill-to, default bill-to, lov.default pay-from, default pay-from |
Main Table | Bring in LOV Values |
Main Table Alias | lov |
Joins | Input: DL Prior Sales Rep Affiliation, psra, Left |
Join Expressions | "lov"."algn_struc_cd" = "psra"."algn_struc_cd" AND "lov"."participant_id" = "psra"."participant_id" AND Cast("lov"."efftv_start_dt" as date) = Cast("psra"."geo_efftv_start_dt" as date) AND Cast("lov"."efftv_end_dt" as date) = Cast("psra"."geo_efftv_end_dt" as date) AND "lov"."org id prefix" = "psra"."org id prefix" AND "lov"."default bill-to" = "psra"."default bill-to" AND "lov"."default pay-from" = "psra"."default pay-from", lov_Left_psra |
Output Columns | lov.participant_id, lov_participant_id, lov.algn_struc_cd, lov_algn_struc_cd, lov.geo_id, lov_geo_id, lov.core_temp_cd, lov_core_temp_cd, lov.split_pct, lov_split_pct, lov.efftv_start_dt, efftv_start_dt, lov.efftv_end_dt, efftv_end_dt, lov.last_updated_dt, last_updated_dt, lov.org id prefix, lov_org id prefix, lov.default bill-to, lov_default bill-to, lov.default pay-from, lov_default pay-from, psra.participant_id, psra_participant_id, psra.algn_struc_cd, psra_algn_struc_cd, psra.geo_efftv_start_dt, psra_geo_efftv_start_dt, psra.geo_efftv_end_dt, psra_geo_efftv_end_dt, psra.geo_id, psra_geo_id, psra.org id prefix, psra_org id prefix, psra.default bill-to, psra_default bill-to, psra.default pay-from, psra_default pay-from, psra.split_pct, psra_split_pct, psra.core_temp_cd, psra_core_temp_cd |
Column Mapping | psra_participant_id, participant_id, psra_algn_struc_cd, algn_struc_cd, psra_geo_efftv_start_dt, geo_efftv_start_dt, psra_geo_efftv_end_dt, geo_efftv_end_dt, psra_geo_id, geo_id, psra_org id prefix, org id prefix, psra_default bill-to, default bill-to, psra_default pay-from, default pay-from, lov_split_pct, split_pct, lov_core_temp_cd, core_temp_cd |
Main Table | Find new/ changed/ deleted UN-MATCHED -Column |
Main Table Alias | fncdr |
Joins | Bring in LOV Values, lov, Left |
Join Expressions | cast("fncdr"."geo_efftv_end_dt" as date) <> cast("lov"."efftv_end_dt" as date) AND cast("fncdr"."geo_efftv_start_dt" as date) = cast("lov"."efftv_start_dt" as date) AND "fncdr"."algn_struc_cd" = "lov"."algn_struc_cd" AND "fncdr"."geo_id" = "lov"."geo_id", fncdr_Left_lov |
Output Columns | fncdr.participant_id, participant_id, fncdr.algn_struc_cd, algn_struc_cd, fncdr.geo_efftv_start_dt, geo_efftv_start_dt, fncdr.geo_efftv_end_dt, geo_efftv_end_dt, fncdr.geo_id, geo_id, fncdr.org id prefix, org id prefix, fncdr.default bill-to, default bill-to, fncdr.default pay-from, default pay-from, fncdr.split_pct, split_pct, fncdr.core_temp_cd, core_temp_cd, lov.participant_id, lov_participant_id, lov.algn_struc_cd, lov_algn_struc_cd, lov.geo_id, lov_geo_id, lov.core_temp_cd, lov_core_temp_cd, lov.split_pct, lov_split_pct, lov.efftv_start_dt, efftv_start_dt, lov.efftv_end_dt, efftv_end_dt, lov.last_updated_dt, last_updated_dt, lov.org id prefix, lov_org id prefix, lov.default bill-to, lov_default bill-to, lov.default pay-from, lov_default pay-from |
Main Table | Check Deleted for Changed - Matched |
Main Table Alias | cdfc |
Joins | DL Prior Org, dlpo, Left |
Join Expressions | "cdfc"."algn_struc_cd" = "dlpo"."algn_struc_cd" AND "cdfc"."geo_id" = "dlpo"."geo_id", cdfc_Left_dlpo |
Output Columns | cdfc.participant_id, participant_id, cdfc.algn_struc_cd, algn_struc_cd, cdfc.geo_efftv_start_dt, geo_efftv_start_dt, cdfc.geo_efftv_end_dt, geo_efftv_end_dt, cdfc.geo_id, geo_id, cdfc.org id prefix, org id prefix, cdfc.default bill-to, default bill-to, cdfc.default pay-from, default pay-from, cdfc.split_pct, split_pct, cdfc.core_temp_cd, core_temp_cd, dlpo.geo_id, dlpo_geo_id |
Main Table | Hold New Territories - Un-Matched |
Main Table Alias | hntun |
Joins | Get Ended Territories: Excluded, get, Inner |
Join Expressions | "hntun"."algn_struc_cd" = "get"."algn_struc_cd" AND "hntun"."geo_id" = "get"."geo_id" AND Cast("hntun"."efftv_start_dt" as date) = Cast("get"."geo_efftv_start_dt" as date), hntun_Inner_get |
Output Columns | hntun.algn_struc_cd, algn_struc_cd, hntun.participant_id, participant_id, hntun.efftv_start_dt, efftv_start_dt, hntun.efftv_end_dt, efftv_end_dt, hntun.geo_id, geo_id, hntun.org id prefix, org id prefix, hntun.default bill-to, default bill-to, hntun.default pay-from, default pay-from, hntun.split_pct, split_pct, hntun.core_temp_cd, core_temp_cd |
Main Table | Bring New/Changed and Deleted |
Main Table Alias | bncd |
Joins | Get Latest Geography: Filter Not Nation, glgfa, Inner |
Join Expressions | "bncd"."algn_struc_cd" = "glgfa"."algn_struc_cd" AND "bncd"."geo_id" = "glgfa"."geo_id" , bncd_Inner_glgfa |
Output Columns | bncd.participant_id, participant_id, bncd.algn_struc_cd, algn_struc_cd, bncd.geo_id, geo_id, bncd.split_pct, split_pct, bncd.efftv_start_dt, efftv_start_dt, bncd.efftv_end_dt, efftv_end_dt, bncd.delete indicator, delete indicator, bncd.org id prefix, org id prefix, bncd.default bill-to, default bill-to, bncd.default pay-from, default pay-from, bncd.core_temp_cd, core_temp_cd, glgfa.level_cd, level_cd |
Include Input Columns | Yes |
Calculations | to_char(cast("efftv_start_dt" as date),'yyyymmdd'), efftv_start_dt, to_char(cast("efftv_end_dt" as date),'yyyymmdd'), efftv_end_dt, to_char(cast("last_updated_dt" as date),'yyyymmdd'), last_updated_dt, Cast(Cast("split_pct" as decimal(10,4)) as varchar(255)), split_pct |
Column Mapping | provider id, Provider ID, participant_id, World Wide ID, geo_id, Organization ID, efftv_start_dt, Affiliation Effective Date, efftv_end_dt, Affiliation End Date, primary rep indicator, Primary Rep Indicator, delete indicator, Delete Indicator, last_updated_dt, Last Update Date, sales rep bill-to indicator, Sales Rep Bill-to indicator, default bill-to, Bill-To Universal Customer Number, sales rep pay-from indicator, Sales Rep Pay-From Indicator, default pay-from, Pay-From Universal Customer Number, sales rep universal customer n, Sales Rep Universal Customer Number, split_pct, Split Percentage, algn_struc_cd, Account Alignment Structure Code, core_temp_cd, Core Temp Indicator |
Columns | provider id, world wide id, organization id, affiliation effective date, affiliation end date, primary rep indicator, delete indicator, last update date, sales rep bill-to indicator, bill-to universal customer number, sales rep pay-from indicator, pay-from universal customer number, sales rep universal customer number, split percentage, account alignment structure code, core temp indicator |
Schema | ${Schema_Default} |
Target Table Name | outbound_dest_eth_dl_mdm21_sales_rep_affiliation |
Fix Data Type Mismatches | No |
Column Mapping | provider id, provider id, world wide id, world wide id, organization id, organization id, affiliation effective date, affiliation effective date, affiliation end date, affiliation end date, primary rep indicator, primary rep indicator, delete indicator, delete indicator, last update date, last update date, sales rep bill-to indicator, sales rep bill-to indicator, bill-to universal customer number, bill-to universal customer number, sales rep pay-from indicator, sales rep pay-from indicator, pay-from universal customer number, pay-from universal customer number, sales rep universal customer number, sales rep universal customer number, split percentage, split percentage, account alignment structure code, account alignment structure code, core temp indicator, core temp indicator |
Truncate | Truncate |
Automatic Compression | No |
Main Table | Create Constants |
Main Table Alias | cc |
Joins | Sales Roster, sr, Left |
Join Expressions | "cc"."participant_id" = "sr"."jj_ww_id", cc_Left_sr |
Output Columns | cc.participant_id, participant_id, cc.algn_struc_cd, algn_struc_cd, cc.geo_id, geo_id, cc.split_pct, split_pct, cc.efftv_start_dt, efftv_start_dt, cc.efftv_end_dt, efftv_end_dt, cc.delete indicator, delete indicator, cc.org id prefix, org id prefix, cc.default bill-to, default bill-to, cc.default pay-from, default pay-from, cc.core_temp_cd, core_temp_cd, cc.level_cd, level_cd, cc.provider id, provider id, cc.organization id , organization id, cc.primary rep indicator, primary rep indicator, cc.last_updated_dt, last_updated_dt, cc.sales rep pay-from indicator, sales rep pay-from indicator, cc.sales rep universal customer n, sales rep universal customer n, cc.sales rep bill-to indicator, sales rep bill-to indicator, sr.jj_ww_id, wwid |
Main Table | Exclude Future Dated: Include |
Main Table Alias | efdm |
Joins | Filter Out Not In Sales Roster: Un-Matched, fonsrun, Left |
Join Expressions | "efdm"."participant_id" = "fonsrun"."participant_id", efdm_Left_fonsrun |
Output Columns | efdm.participant_id, participant_id, efdm.algn_struc_cd, algn_struc_cd, efdm.geo_id, geo_id, efdm.core_temp_cd, core_temp_cd, efdm.split_pct, split_pct, efdm.efftv_start_dt, geo_efftv_start_dt, efdm.efftv_end_dt, geo_efftv_end_dt, efdm.last_updated_dt, last_updated_dt, efdm.org id prefix, org id prefix, efdm.default bill-to, default bill-to, efdm.default pay-from, default pay-from, fonsrun.participant_id, fonsrun_participant_id |
Column Mapping | participant_id, participant_id, algn_struc_cd, algn_struc_cd, geo_efftv_start_dt, geo_efftv_start_dt, geo_efftv_end_dt, geo_efftv_end_dt, geo_id, geo_id, org id prefix, org id prefix, default bill-to, default bill-to, default pay-from, default pay-from, split_pct, split_pct, core_temp_cd, core_temp_cd, last_updated_dt, last_updated_dt |
Schema | ${Schema_Default} |
Target Table Name | stg_dl_prior_sale_rep_affiliation_bkp |
Fix Data Type Mismatches | No |
Column Mapping | participant_id, participant_id, algn_struc_cd, algn_struc_cd, geo_efftv_start_dt, geo_efftv_start_dt, geo_efftv_end_dt, geo_efftv_end_dt, geo_id, geo_id, org id prefix, org id prefix, default bill-to, default bill-to, default pay-from, default pay-from, last_updated, last_updated |
Truncate | Append |
Schema | ${Schema_Default} |
Target Table Name | stg_dl_prior_sale_rep_affiliation |
Fix Data Type Mismatches | No |
Column Mapping | participant_id, participant_id, algn_struc_cd, algn_struc_cd, geo_efftv_start_dt, geo_efftv_start_dt, geo_efftv_end_dt, geo_efftv_end_dt, geo_id, geo_id, org id prefix, org id prefix, default bill-to, default bill-to, default pay-from, default pay-from |
Truncate | Truncate |
Automatic Compression | No |
Main Table | Excluded Not in Roster - Un-Matched |
Main Table Alias | enrun |
Joins | Input: DL Prior Sales Rep Affiliation, psra, Inner |
Join Expressions | "enrun"."algn_struc_cd" = "psra"."algn_struc_cd" AND "enrun"."geo_id" = "psra"."geo_id" AND cast("enrun"."geo_efftv_start_dt" as date) = cast("psra"."geo_efftv_start_dt" as date) AND "enrun"."participant_id" = "psra"."participant_id", enrun_Inner_psra |
Output Columns | enrun.participant_id, participant_id, enrun.algn_struc_cd, algn_struc_cd, enrun.geo_id, geo_id, enrun.core_temp_cd, core_temp_cd, enrun.split_pct, split_pct, enrun.geo_efftv_start_dt, geo_efftv_start_dt, enrun.geo_efftv_end_dt, geo_efftv_end_dt, enrun.last_updated_dt, last_updated_dt, enrun.org id prefix, org id prefix, enrun.default bill-to, default bill-to, enrun.default pay-from, default pay-from, psra.participant_id, psra_participant_id |
Column Mapping | participant_id, participant_id, algn_struc_cd, algn_struc_cd, geo_efftv_start_dt, efftv_start_dt, geo_efftv_end_dt, efftv_end_dt, geo_id, geo_id, org id prefix, org id prefix, default bill-to, default bill-to, default pay-from, default pay-from, split_pct, split_pct, core_temp_cd, core_temp_cd |
Column Mapping | lov_participant_id, participant_id, lov_algn_struc_cd, algn_struc_cd, lov_geo_id, geo_id, lov_core_temp_cd, core_temp_cd, lov_split_pct, split_pct, efftv_start_dt, efftv_start_dt, efftv_end_dt, efftv_end_dt, last_updated_dt, last_updated_dt, lov_org id prefix, org id prefix, lov_default bill-to, default bill-to, lov_default pay-from, default pay-from |
Include Input Columns | Yes |
Calculations | CASE WHEN Cast("efftv_start_dt" as date) > cast(convert_timezone('EST', Cast(getdate() as date)) as date) OR Cast("efftv_end_dt" as date) >= cast(convert_timezone('EST', Cast(getdate() as date)) as date) AND Cast("efftv_end_dt" as date) != Cast('12-31-9999' as date) THEN 'Yes' ELSE 'No' END /*CASE WHEN Cast("efftv_start_dt" as date) > Cast('2019-12-02' as date) OR (Cast("efftv_end_dt" as date) >= Cast('2019-12-02' as date) AND Cast("efftv_end_dt" as date) != Cast('12-31-9999' as date)) THEN 'Yes' ELSE 'No' END */ , Is_Future_Dated_Flag, Cast(getdate() as date), Process Date |
Main Table | Filter New Territories |
Main Table Alias | fnt |
Joins | Exclude Future Dated Records - Filter - Yes, ifdr, Left |
Join Expressions | "fnt"."algn_struc_cd" = "ifdr"."algn_struc_cd" AND "fnt"."participant_id" = "ifdr"."participant_id" AND "fnt"."geo_id" = "ifdr"."geo_id" AND "fnt"."efftv_start_dt" = "ifdr"."efftv_start_dt" AND "fnt"."efftv_end_dt" = "ifdr"."efftv_end_dt", fnt_Left_ifdr |
Output Columns | fnt.participant_id, participant_id, fnt.algn_struc_cd, algn_struc_cd, fnt.geo_id, geo_id, fnt.core_temp_cd, core_temp_cd, fnt.split_pct, split_pct, fnt.efftv_start_dt, efftv_start_dt, fnt.efftv_end_dt, efftv_end_dt, fnt.last_updated_dt, last_updated_dt, fnt.org id prefix, org id prefix, fnt.default bill-to, default bill-to, fnt.default pay-from, default pay-from, ifdr.algn_struc_cd, ifdr_algn_struc_cd |
Main Table | Exclude Future Dated: Exclude |
Main Table Alias | gofd |
Joins | Input: DL Prior Sales Rep Affiliation, dlpsra, Inner |
Join Expressions | "gofd"."algn_struc_cd" = "dlpsra"."algn_struc_cd" AND "gofd"."geo_id" = "dlpsra"."geo_id" AND "gofd"."efftv_start_dt" = "dlpsra"."geo_efftv_start_dt" AND "gofd"."efftv_end_dt" = "dlpsra"."geo_efftv_end_dt", gofd_Inner_dlpsra |
Output Columns | gofd.participant_id, participant_id, gofd.algn_struc_cd, algn_struc_cd, gofd.geo_id, geo_id, gofd.core_temp_cd, core_temp_cd, gofd.split_pct, split_pct, gofd.efftv_start_dt, geo_efftv_start_dt, gofd.efftv_end_dt, geo_efftv_end_dt, gofd.last_updated_dt, last_updated_dt, gofd.org id prefix, org id prefix, gofd.default bill-to, default bill-to, gofd.default pay-from, default pay-from, dlpsra.participant_id, dlpsra_participant_id |
Main Table | Identify Future Dated Records: Active |
Main Table Alias | ifdr |
Joins | DL Prior Org, dlpo, Left |
Join Expressions | "ifdr"."algn_struc_cd" = "dlpo"."algn_struc_cd" AND "ifdr"."geo_id" = "dlpo"."geo_id" , ifdr_Left_dlpo |
Output Columns | ifdr.algn_struc_cd, algn_struc_cd, ifdr.participant_id, participant_id, ifdr.efftv_start_dt, efftv_start_dt, ifdr.efftv_end_dt, efftv_end_dt, ifdr.geo_id, geo_id, ifdr.org id prefix, org id prefix, ifdr.default bill-to, default bill-to, ifdr.default pay-from, default pay-from, ifdr.split_pct, split_pct, ifdr.core_temp_cd, core_temp_cd, dlpo.algn_struc_cd, dlpo_algn_struc_cd |
Main Table | Bring in LOV Values |
Main Table Alias | lov |
Joins | DL Prior Org (2), dlpo, Inner |
Join Expressions | "lov"."algn_struc_cd" = "dlpo"."algn_struc_cd" AND "lov"."geo_id" = "dlpo"."geo_id", lov_Inner_dlpo |
Output Columns | lov.participant_id, participant_id, lov.algn_struc_cd, algn_struc_cd, lov.geo_id, geo_id, lov.core_temp_cd, core_temp_cd, lov.split_pct, split_pct, lov.efftv_start_dt, efftv_start_dt, lov.efftv_end_dt, efftv_end_dt, lov.last_updated_dt, last_updated_dt, lov.org id prefix, org id prefix, lov.default bill-to, default bill-to, lov.default pay-from, default pay-from, dlpo.geo_id, dlpo_geo_id, dlpo.algn_struc_cd, dlpo_algn_struc_cd |
Main Table | Part-Geo: Format Dates Filter Division |
Main Table Alias | pgfd |
Joins | Param: Geo Hire Cal Dates Filter Division, ghfd, Left |
Join Expressions | "pgfd"."algn_struc_cd" = "ghfd"."upper_algn_struc_cd" AND cast("pgfd"."efftv_start_dt" as date) <= cast("ghfd"."efftv_end_dt" as date) AND cast("pgfd"."efftv_end_dt" as date) >= cast("ghfd"."efftv_start_dt" as date), pgfd_Left_ghfd |
Output Columns | pgfd.participant_id, participant_id, pgfd.algn_struc_cd, algn_struc_cd, pgfd.geo_id, geo_id, pgfd.core_temp_cd, core_temp_cd, pgfd.split_pct, split_pct, pgfd.last_updated_dt, last_updated_dt, pgfd.efftv_start_dt, efftv_start_dt, pgfd.efftv_end_dt, efftv_end_dt, ghfd.lower_algn_struc_cd, lower_algn_struc_cd, ghfd.efftv_start_dt, new_efftv_start_dt, ghfd.efftv_end_dt, new_efftv_end_dt |
Main Table | Team-Geo: Rename Columns |
Main Table Alias | tga |
Joins | Input: Fiscal Calendar 2, fc, Left |
Join Expressions | cast("tga"."efftv_start_dt" as date) = cast("fc"."efftv_start_dt" as date), tga_Left_fc |
Output Columns | tga.algn_struc_cd, algn_struc_cd, tga.geo_id, geo_id, tga.team_id, team_id, tga.split_pct, split_pct, tga.efftv_start_dt, tga_efftv_start_dt, tga.efftv_end_dt, tga_efftv_end_dt, tga.last_updated, last_updated, fc.fiscal_year_wk_start_dt, fiscal_year_wk_start_dt |
Main Table | Team-Geo: Start Date |
Main Table Alias | tgsd |
Joins | Input: Fiscal Calendar 2, fc, Left |
Join Expressions | cast("tgsd"."tga_efftv_end_dt" as date) = cast("fc"."efftv_end_dt" as date), tgsd_Left_fc |
Output Columns | tgsd.algn_struc_cd, algn_struc_cd, tgsd.geo_id, geo_id, tgsd.team_id, team_id, tgsd.split_pct, split_pct, tgsd.last_updated, last_updated, tgsd.fiscal_year_wk_start_dt, tg_efftv_start_dt, fc.fiscal_year_wk_end_dt, tg_efftv_end_dt |
Main Table | Geographies - Format Dates |
Main Table Alias | g |
Joins | Aggregate Geographies, ag, Inner |
Join Expressions | "g"."algn_struc_cd" = "ag"."algn_struc_cd" AND "g"."geo_id" = "ag"."geo_id" AND cast("g"."efftv_start_dt" as date) = cast("ag"."max_efftv_start_dt" as date), g_Inner_ag |
Output Columns | g.algn_struc_cd, algn_struc_cd, g.geo_id, geo_id, g.geo_nm, geo_nm, g.level_cd, level_cd, g.efftv_start_dt, efftv_start_dt, g.efftv_end_dt, efftv_end_dt, g.last_updated, last_updated |
Main Table | Part-Geo: Rename Columns |
Main Table Alias | pga |
Joins | Input: Fiscal Calendar, fc, Left |
Join Expressions | cast("pga"."geo_efftv_start_dt" as date) = cast("fc"."efftv_start_dt" as date), pga_Left_fc |
Output Columns | pga.algn_struc_cd, algn_struc_cd, pga.participant_id, participant_id, pga.geo_id, geo_id, pga.core_temp_cd, core_temp_cd, pga.split_pct, split_pct, pga.last_updated, last_updated, pga.geo_efftv_start_dt, geo_efftv_start_dt, pga.geo_efftv_end_dt, geo_efftv_end_dt, fc.fiscal_year_wk_start_dt, fiscal_year_wk_start_dt |
Main Table | Part-Geo: Start Date |
Main Table Alias | pgsd |
Joins | Input: Fiscal Calendar, fc, Left |
Join Expressions | cast("pgsd"."geo_efftv_end_dt" as date) = cast("fc"."efftv_end_dt" as date), pgsd_Left_fc |
Output Columns | pgsd.algn_struc_cd, algn_struc_cd, pgsd.participant_id, participant_id, pgsd.geo_id, geo_id, pgsd.core_temp_cd, core_temp_cd, pgsd.split_pct, split_pct, pgsd.last_updated, last_updated_dt, pgsd.fiscal_year_wk_start_dt, pg_efftv_start_dt, fc.fiscal_year_wk_end_dt, pg_efftv_end_dt |
Include Input Columns | Yes |
Calculations | CASE WHEN "pg_efftv_start_dt" IS NULL THEN to_date('01/01/1900' ,'MM/DD/YYYY') ELSE "pg_efftv_start_dt" END , efftv_start_dt, CASE WHEN "pg_efftv_end_dt" IS NULL THEN to_date('12/31/9999' ,'MM/DD/YYYY') ELSE "pg_efftv_end_dt" END , efftv_end_dt, cast("last_updated_dt" as date), last_updated_dt |
Schema | ${Schema_Default} |
Table Name | outbound_src_sales_roster |
Column Names | reg_no, reg_nm, div_no, div_nm, terr_no, terr_nm, fran_cd, posn_cd, posn_nm, pos_refnc_cd, area_id, first_nm, last_nm, vmail_no, addr_ln_1_txt, apt_no, addr_ln_2_txt, city_nm, stt_cd, postl_cd, tel_no, ssan_no, pgr_1_no, pin_1_no, pgr_2_no, pin_2_no, fax_no, cell_tel_no, sfa_pda_ovrrd, sex_cd, stat_cd, email_addr_text, cstctr_no, bday_dt, hire_dt, spse_nm, spse_ssn_no, chldn_txt, pg_no, actv_ind, jj_ww_id, dendrite_id |
Trim Columns | No |
Schema | ${Schema_Default} |
Target Table Name | dl_prior_sale_rep_affiliation |
Fix Data Type Mismatches | No |
Column Mapping | participant_id, participant_id, algn_struc_cd, algn_struc_cd, geo_efftv_start_dt, geo_efftv_start_dt, geo_efftv_end_dt, geo_efftv_end_dt, geo_id, geo_id, org id prefix, org id prefix, default bill-to, default bill-to, default pay-from, default pay-from |
Truncate | Truncate |
Automatic Compression | No |
Script | import datetime x = datetime.datetime.now() x=str(x).split('.') x=x[0] print('time_stamp :'+str(x)) print('Archive_temp_current_table :'+str(Archive_temp_current_table)) print('Archive_temp_current_file :'+str(Archive_temp_current_file)) Archive_temp_current_file=Archive_temp_current_file+'_'+x+'__' print('Archive_temp_current_file upt :'+str(Archive_temp_current_file)) context.updateVariable('Archive_temp_current_file', str(Archive_temp_current_file)) print('Archive_type :'+Archive_type) #dynamically change path for Archive if Archive_type=='ONEMD': S3_temp_Archive_Location=S3_ONEMD_Archive_Location print('Archive Locations :'+str(S3_temp_Archive_Location)) elif Archive_type=='MDM21': S3_temp_Archive_Location=S3_MDM21_Archive_Location print('Archive Locations :'+str(S3_temp_Archive_Location)) else: S3_temp_Archive_Location=S3_EUSS_Archive_Location print('Archive Locations :'+str(S3_temp_Archive_Location)) |
Interpreter | Jython |
Schema | ${Schema_Default} |
Table Name | ${Archive_temp_current_table} |
S3 URL Location | ${S3_ONEMD_Archive_Location} |
S3 Object Prefix | ${Archive_temp_current_file} |
IAM Role Arn | arn:aws:iam::775229046089:role/RedshiftS3Athna |
Generate Manifest | No |
Data File Type | Delimited |
Delimiter | | |
Compress Data | Yes |
Compression Type | GZIP |
Null As | |
Escape | No |
Allow Overwrites | Yes |
Parallel | No |
Add Quotes | No |
Max File Size (MB) | |
Include Header | No |
Encryption | None |
Schema | ${Schema_Default} |
Table Name | ${Archive_temp_current_table} |
S3 URL Location | ${S3_MDM21_Archive_Location} |
S3 Object Prefix | ${Archive_temp_current_file} |
IAM Role Arn | arn:aws:iam::775229046089:role/RedshiftS3Athna |
Generate Manifest | No |
Data File Type | Delimited |
Delimiter | | |
Compress Data | Yes |
Compression Type | GZIP |
Null As | |
Escape | No |
Allow Overwrites | Yes |
Parallel | No |
Add Quotes | No |
Max File Size (MB) | |
Include Header | No |
Encryption | None |
Schema | ${Schema_Default} |
Table Name | ${Archive_temp_current_table} |
S3 URL Location | ${S3_EUSS_Archive_Location} |
S3 Object Prefix | ${Archive_temp_current_file} |
IAM Role Arn | arn:aws:iam::775229046089:role/RedshiftS3Athna |
Generate Manifest | No |
Data File Type | Delimited |
Delimiter | | |
Compress Data | Yes |
Compression Type | GZIP |
Null As | |
Escape | No |
Allow Overwrites | Yes |
Parallel | No |
Add Quotes | No |
Max File Size (MB) | |
Include Header | No |
Encryption | None |